#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Time      :2024/2/18 10:46
# Author    :lys
from django.core.paginator import Paginator

from app import models, views
from utils import call
from django.db.models import Q
from datetime import datetime, timedelta, date
import re
from django.db import connection, OperationalError

from utils.call import create_log, create_Information


def boat_ticket(request, search):
    # 角色ID：value 查询内容：search
    page_num = int(request.GET.get('page', 1))
    bg_hs = int(request.GET.get('limit', 10))
    re_url = request.GET.get('type', None)
    bjqx = call.edit_grant(request, re_url)
    scqx = call.delete_grant(request, re_url)
    ckqx = call.see_grant(request, re_url)

    if search != None and search != '':
        sear = []
        user_all = models.DvTicket.objects.filter(Q(name__contains=search)).order_by('id')
        sear_base = user_all[(page_num - 1) * bg_hs:page_num * bg_hs]
        for se in sear_base.values('id', 'name', 'agen_name', 'price', 'departure_time', 'end_time', 'duration'):
            se['duration'] = se['duration'] + '分钟'
            se['price'] = '¥ ' + se['price']
            se['departures'] = '营业时间内滚动开船'
            se['sfkbj'] = bjqx
            se['sfksc'] = scqx
            se['sfkck'] = ckqx
            sear.append(se)
        return {"code": 0, 'page_num': page_num, 'bg_hs': bg_hs, 'count': len(user_all), 'data': sear}
    else:
        li = []
        # all = models.SysUser.objects.filter(~Q(account='admin')).order_by('id')
        all = models.DvTicket.objects.filter().order_by('id')
        aggregate = all[(page_num - 1) * bg_hs:page_num * bg_hs]
        for n in aggregate.values('id', 'name', 'agen_name', 'price', 'departure_time', 'end_time', 'duration'):
            n['duration'] = n['duration'] + '分钟'
            n['price'] = '¥ ' + n['price']
            n['departures'] = '营业时间内滚动开船'
            n['sfkbj'] = bjqx
            n['sfksc'] = scqx
            n['sfkck'] = ckqx
            li.append(n)
        return {"code": 0, 'page_num': page_num, 'bg_hs': bg_hs, 'count': len(all), 'data': li}


def detail_ticket(request, id):
    cur = connection.cursor()
    cur.execute("select * from dv_ticket where id = %s", [id])
    ticket_data = cur.fetchall()
    data = ticket_data[0]
    call.create_log(request, 'select', '查看船票', ticket_data[0][1])
    cur.close()
    return data


def inser_ticket(request):
    data = request.POST
    ex_t = models.DvTicket.objects.filter(name=data['base[name]'])
    if len(ex_t) == 0:
        departure_time = data['base[departure_time]'].replace('T', ' ')
        format_str = "%H:%M"
        # 将字符串解析为 datetime 对象
        time_de = datetime.strptime(departure_time, format_str)
        # 获取当前时间

        end_time = data['base[end_time]'].replace('T', ' ')
        time_end = datetime.strptime(end_time, format_str)

        if time_end > time_de:
            models.DvTicket.objects.create(name=data['base[name]'], agen_name=data['base[agen_name]'],
                                           price=data['base[price]'], departure_time=data['base[departure_time]'],
                                           end_time=data['base[end_time]'], duration=data['base[duration]'])
            create_log(request, 'create', '新增船票', data['base[name]'])
            return True
        else:
            return 'error_end_time'
    else:
        return 'exist_boat'


def edit_ticket(request, id):
    data = request.POST
    ex_t = models.DvTicket.objects.filter(name=data['base[name]'])
    if len(ex_t) == 0 or ex_t.first().id == int(id):
        departure_time = data['base[departure_time]'].replace('T', ' ')
        format_str = "%H:%M"
        # 将字符串解析为 datetime 对象
        time_de = datetime.strptime(departure_time, format_str)
        end_time = data['base[end_time]'].replace('T', ' ')
        time_end = datetime.strptime(end_time, format_str)
        if time_end > time_de:
            models.DvTicket.objects.filter(id=id).update(name=data['base[name]'],
                                                         agen_name=data['base[agen_name]'],
                                                         price=data['base[price]'],
                                                         departure_time=data['base[departure_time]'],
                                                         end_time=data['base[end_time]'],
                                                         duration=data['base[duration]'])
            create_log(request, 'update', '修改船票信息', data['base[name]'])
            return True
        else:
            return 'error_end_time'
    else:
        return 'exist_boat'


def extract_province(id_card):
    province_code = id_card[:2]
    province_mapping = {
        '11': '北京市', '12': '天津市', '13': '河北省', '14': '山西省', '15': '内蒙古自治区',
        '21': '辽宁省', '22': '吉林省', '23': '黑龙江省', '31': '上海市', '32': '江苏省',
        '33': '浙江省', '34': '安徽省', '35': '福建省', '36': '江西省', '37': '山东省',
        '41': '河南省', '42': '湖北省', '43': '湖南省', '44': '广东省', '45': '广西壮族自治区',
        '46': '海南省', '50': '重庆市', '51': '四川省', '52': '贵州省', '53': '云南省',
        '54': '西藏自治区', '61': '陕西省', '62': '甘肃省', '63': '青海省', '64': '宁夏回族自治区',
        '65': '新疆维吾尔自治区', '71': '台湾省', '81': '香港特别行政区', '82': '澳门特别行政区'
    }
    return province_mapping.get(province_code)


def calculate_age(id_card):
    birth_date_str = id_card[6:14]
    birth_date = datetime.strptime(birth_date_str, '%Y%m%d')
    current_date = datetime.now()
    age = current_date.year - birth_date.year - (
            (current_date.month, current_date.day) < (birth_date.month, birth_date.day))

    return age


def buy_ticket(request):
    data = request.POST
    user_id = request.session['user_id']
    user_data = models.SysUser.objects.filter(id=user_id)
    account = user_data[0].account
    # 获取当前日期(字符串)
    current_date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")  # str 2024-03-06 18:00:04
    today = current_date.split(' ')[0]  # str 2024-03-06

    # 日期格式化(转为datetime.datetime)
    current_date = datetime.strptime(current_date, "%Y-%m-%d %H:%M:%S")  # datetime.datetime 2024-03-06 18:00:04
    today = datetime.strptime(today, "%Y-%m-%d")  # datetime.datetime 2024-03-06 18:00:04
    tour_date = datetime.strptime(data['base[tour_date]'], "%Y-%m-%d")  # datetime.datetime 2024-03-06 18:00:04

    if tour_date >= today:
        # 获取当前时间后的第十五天
        fifteenth_day = current_date + timedelta(days=15)  # datetime.datetime 2024-03-06 18:00:04
        if tour_date < fifteenth_day:
            ex_t = models.DvOrderInfo.objects.filter(BoatName=data['base[name]'], AgenName=data['base[agen_name]'],
                                                     BookingTime=data['base[tour_date]'],
                                                     PassengerName=data['base[yk_name]'], IdCard=data['base[id_card]'])
            if len(ex_t) == 0:
                ban_type_key = data['ban_type']
                if ban_type_key == 0:
                    ban_type = '白班'
                else:
                    ban_type = '夜班'
                price = re.findall(r'\d+', data['base[price]'])
                quarter = (tour_date.month - 1) // 3 + 1
                tour_date_split = str(tour_date.date()).split('-')
                year_m = tour_date_split[0] + tour_date_split[1]
                try:
                    models.DvOrderInfo.objects.create(CreatDate=current_date, OrderId=views.uid(),
                                                      BookingTime=tour_date, ProductName=data['base[name]'] + ban_type,
                                                      BoatName=data['base[name]'], DayOrNight=ban_type_key,
                                                      Price=float(price[0]),
                                                      IdCard=data['base[id_card]'], PassengerName=data['base[yk_name]'],
                                                      Status=0, AgenName=data['base[agen_name]'],
                                                      Years=tour_date.year, Months=int(year_m),
                                                      Quart=quarter,
                                                      City=data['base[origin]'],
                                                      province=extract_province(data['base[id_card]']),
                                                      Sex=int(data['base[sex]']),
                                                      Age=calculate_age(data['base[id_card]']), user_id=user_id,
                                                      account=account)
                    create_log(request, 'buy', '购买船票', data['base[name]'] + ban_type)
                    return True
                except OperationalError as e:
                    # 如果捕获到 OperationalError 异常
                    # 可以从异常对象中获取错误消息
                    error_code, error_message = e.args
                    if error_code == 1526:
                        '''数据库分区介绍
                        数据库分区规则是以当前日期减去0000-00-00而得到的天数
                        例如2024-02-29，会得到739310，但是这个天数不会包含02-29当天本身
                        因此当获取到用户的预订时间后，假如报错分区错误，会获取订单日期的值，并以年份+月份作为分区名称，
                        同时计算订单日期的下一个月的第一天，以此日期计算分区值'''
                        # 获取分区名称
                        try:
                            par_name = 'p' + year_m
                            # 计算下一个月的第一天的日期
                            par_date = (tour_date.replace(day=1) + timedelta(days=32)).replace(day=1)
                            # 构建分区sql
                            with connection.cursor() as cur:
                                cur.execute("SELECT TO_DAYS(%s);", (par_date,))
                                extracted_number = cur.fetchone()[0]
                                cur.execute(
                                    f"ALTER TABLE dv_orderinfo ADD PARTITION (PARTITION {par_name} VALUES LESS THAN ({extracted_number}) ENGINE = InnoDB);"
                                )
                                create_log(request, 'partition', '构建新分区', f"{par_name} {extracted_number}")

                            models.DvOrderInfo.objects.create(CreatDate=current_date, OrderId=views.uid(),
                                                              BookingTime=tour_date,
                                                              ProductName=data['base[name]'] + ban_type,
                                                              BoatName=data['base[name]'], DayOrNight=ban_type_key,
                                                              Price=float(price[0]), IdCard=data['base[id_card]'],
                                                              PassengerName=data['base[yk_name]'], Status=0,
                                                              Years=tour_date.year, Months=int(year_m),
                                                              Quart=quarter,
                                                              City=data['base[origin]'],
                                                              province=extract_province(data['base[id_card]']),
                                                              Sex=int(data['base[sex]']),
                                                              Age=calculate_age(data['base[id_card]']), user_id=user_id,
                                                              account=account)
                            create_log(request, 'buy', '购买船票', data['base[name]'] + ban_type)
                            return True
                        except Exception as e:
                            return 'partition_error'
                    else:
                        return 'error'
            else:
                return 'ex_ticket'
        else:
            return 'error_date'
    else:
        return 'error_end_time'

def row_to_dict(row, cursor):
    """
    将数据库结果转换为字典
    """
    desc = cursor.description
    return dict(zip([col[0] for col in desc], row))


def order_data(request, status):
    search = request.GET.get('keyword', '')
    agen_name = request.GET.get('agenname', '')
    passenger_name = request.GET.get('passengername', '')
    page_num = int(request.GET.get('page', 1))
    bg_hs = int(request.GET.get('limit', 10))

    re_url = request.GET.get('type', None)
    bjqx = call.edit_grant(request, re_url)
    scqx = call.delete_grant(request, re_url)
    ckqx = call.see_grant(request, re_url)

    user_id = request.session['user_id']
    user_data = models.SysUser.objects.filter(id=user_id)
    account = user_data[0].account
    li = []

    if page_num == 1:
        limit = 0
    else:
        limit = (page_num-1)*bg_hs

    if status:
        if account == 'admin':
            count_sql = f"SELECT COUNT(1) FROM `trttp`.`dv_orderinfo` WHERE Status = 1 and ProductName like '%{search}%' and AgenName like '%{agen_name}%' and PassengerName like '%{passenger_name}%'"
            sql = f"SELECT id, OrderId, ProductName, BookingTime, Price, PassengerName, Sex, IdCard, Status, AgenName FROM `trttp`.`dv_orderinfo` WHERE Status = 1 and ProductName like '%{search}%' and AgenName like '%{agen_name}%' and PassengerName like '%{passenger_name}%' ORDER BY `BookingTime` DESC LIMIT {limit},{bg_hs}"
        else:
            count_sql = f"SELECT COUNT(1) FROM `trttp`.`dv_orderinfo` WHERE user_id = '{user_id}' and account = '{account}' and Status = 1 and ProductName like '%{search}%' and AgenName like '%{agen_name}%' and PassengerName like '%{passenger_name}%'"
            sql = f"SELECT id, OrderId, ProductName, BookingTime, Price, PassengerName, Sex, IdCard, Status, AgenName FROM `trttp`.`dv_orderinfo` WHERE user_id = '{user_id}' and account = '{account}' and Status = 1 and ProductName like '%{search}%' and AgenName like '%{agen_name}%' and PassengerName like '%{passenger_name}%' ORDER BY `BookingTime` DESC LIMIT {limit},{bg_hs}"
    else:

        if account == 'admin':
            count_sql = f"SELECT COUNT(1) FROM `trttp`.`dv_orderinfo` WHERE ProductName like '%{search}%' and AgenName like '%{agen_name}%' and PassengerName like '%{passenger_name}%'"
            sql = f"SELECT id, OrderId, ProductName, BookingTime, Price, PassengerName, Sex, IdCard, Status, AgenName FROM `trttp`.`dv_orderinfo` WHERE ProductName like '%{search}%' and AgenName like '%{agen_name}%' and PassengerName like '%{passenger_name}%' ORDER BY `BookingTime` DESC LIMIT {limit},{bg_hs}"
        else:
            count_sql = f"SELECT COUNT(1) FROM `trttp`.`dv_orderinfo` WHERE user_id = '{user_id}' and account = '{account}' and ProductName like '%{search}%' and AgenName like '%{agen_name}%' and PassengerName like '%{passenger_name}%'"
            sql = f"SELECT id, OrderId, ProductName, BookingTime, Price, PassengerName, Sex, IdCard, Status, AgenName FROM `trttp`.`dv_orderinfo` WHERE user_id = '{user_id}' and account = {account} and ProductName like '%{search}%' and AgenName like '%{agen_name}%' and PassengerName like '%{passenger_name}%' ORDER BY `BookingTime` DESC LIMIT {limit},{bg_hs}"

    cur = connection.cursor()
    cur.execute(sql)
    ship_order_data = cur.fetchall()

    # 将每行结果转为字典
    result_dicts = [row_to_dict(row, cur) for row in ship_order_data]
    for se in result_dicts:
        if se['Sex'] == 1:
            se['Sex'] = '男'
        elif se['Sex'] == 0:
            se['Sex'] = '女'
        if se['Status'] == 0:
            se['Status'] = '有效'
        elif se['Status'] == 1:
            se['Status'] = '已退票'
        else:
            se['Status'] = '暂无'
        se['BookingTime'] = str(se['BookingTime'])
        se['Price'] = '¥ ' + str(se['Price'])
        se['sfkbj'] = bjqx
        se['sfksc'] = scqx
        se['sfkck'] = ckqx
        li.append(se)
    cur.execute(count_sql)
    count = cur.fetchone()[0]
    cur.close()
    return {"code": 0, 'page_num': page_num, 'bg_hs': bg_hs, 'count': int(count), 'data': li}


def detail_order(request, order_id):
    cur = connection.cursor()
    cur.execute("select * from dv_orderinfo where OrderId = %s", [order_id])
    ship_order_data = cur.fetchall()
    data = ship_order_data[0]
    call.create_log(request, 'select', '查看订单', ship_order_data[0][2])
    cur.close()
    return data
